package com.sky.service.impl;

import com.sky.constant.MessageConstant;
import com.sky.dto.OrdersPageQueryDTO;
import com.sky.entity.Orders;
import com.sky.handler.GlobalExceptionHandler;
import com.sky.mapper.OrderMapper;
import com.sky.mapper.ReportMapper;
import com.sky.mapper.UserMapper;
import com.sky.service.ReportService;
import com.sky.service.WorkspaceService;
import com.sky.vo.*;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Service
@Slf4j
public class ReportserviceImpl implements ReportService {

    @Autowired
    private ReportMapper reportMapper;
    @Autowired
    private OrderMapper orderMapper;
    @Autowired
    private UserMapper  userMapper;
    @Autowired
    private WorkspaceService workspaceService;

    /**
     * 营业额统计
     * @param start
     * @param end
     * @return
     */
    @Override
    public TurnoverReportVO getTurnoverStatistics(LocalDate start, LocalDate end) {

        //保存开始日期和结束日期
        LocalDate start9 = start;
        LocalDate end1 = end;

        //1.计算日期并拿到日期字符串
        List<LocalDate> dateList = new ArrayList<>();  //存放各个日期
        dateList.add(start);
        while(!start.equals(end)){
            //日期加1天
            start = start.plusDays(1);
            dateList.add(start);
        }
        //使用:StringUtils.join() 拼接日期字符串
        String date = StringUtils.join(dateList, ",");



        //2.查询各个日期对应的营业额数据
        List<Double> turnoverList = new ArrayList();

        //获取当前时间戳
        long start1 = System.currentTimeMillis();

//        //以下是多次查询数据库, 耗时较长的方式=========>
//        //查询条件: 开始时间, 结束时间, 订单状态=>已完成 5
//        for(LocalDate date1: dateList){
//            LocalDateTime startTime = LocalDateTime.of(date1, LocalTime.MIN);
//            LocalDateTime endTime = LocalDateTime.of(date1, LocalTime.MAX);
//            //select sum(amount) from orders where order_time between #{startTime} and #{endTime} and status = #{status}
//            Double turnover = orderMapper.getTurnover(startTime, endTime, Orders.COMPLETED);
//            turnover = (turnover == null ? 0.0 : turnover);
//            turnoverList.add(turnover);
//        }

        //以下是只查询一次数据库，耗时短的方式
        //获取总的查询时间范围
        LocalDateTime startTime5 = LocalDateTime.of(start9, LocalTime.MIN);
        LocalDateTime endTime5 = LocalDateTime.of(end1, LocalTime.MAX);
        //查询数据库
        List<Orders> turnoverOrders = orderMapper.getTurnoverData(startTime5, endTime5, Orders.COMPLETED);
        //遍历日期集合获取单个日期
        for(LocalDate date1: dateList){
            //获取单个日期的时间范围
            LocalDateTime startTime = LocalDateTime.of(date1, LocalTime.MIN);
            LocalDateTime endTime = LocalDateTime.of(date1, LocalTime.MAX);
            //定义默认营业额
            Double turnover = 0.0;
            //遍历查到的订单集合
            for(Orders orders: turnoverOrders){
                //判断订单时间是否在当前日期内，有则赋予金额，无则默认金额
                if(orders.getOrderTime().isAfter(startTime) && orders.getOrderTime().isBefore(endTime)){
                    //获取订单金额
                    turnover += orders.getAmount().doubleValue();
                }
            }
            turnoverList.add(turnover);
        }


        //获取当前时间戳
        long start2 = System.currentTimeMillis();
        //计算用时
        log.info("查询营业额耗时: {}毫秒", start2 - start1);
        //使用:StringUtils.join() 拼接营业额字符串
        String turnover = StringUtils.join(turnoverList, ",");


        //封装数据
        return TurnoverReportVO
                .builder()
                .dateList(date)
                .turnoverList(turnover)
                .build();
    }

    /**
     * 用户统计
     * @param start
     * @param end
     * @return
     */
    @Override
    public UserReportVO getUserStatistics(LocalDate start, LocalDate end) {

        //保存搜索初始条件
        LocalDate start0 = start;
        LocalDate end0 = end;

        //1.获取日期列表字符串
        List<LocalDate> dateList = new ArrayList<>();
        dateList.add(start);
        while(!start.equals(end)){
            start = start.plusDays(1);
            dateList.add(start);
        }
        String dateString = StringUtils.join(dateList, ",");

        //2.获取用户总数
        //select count(distinct user_id) from orders where order_time between #{startTime} and #{endTime}
        //定义一个存储用户数量的集合
        List<Long> userList = new ArrayList<>();
        //定义一个存储新增用户数量的集合
        List<Long> newUserList = new ArrayList<>();
        //遍历日期列表
        for(LocalDate onDate: dateList){
            //获取开始时间的最小时间
            LocalDateTime startTime = LocalDateTime.of(onDate,LocalTime.MIN);
            //获取结束时间的最大时间
            LocalDateTime endTime = LocalDateTime.of(onDate,LocalTime.MAX);
            //查询数据库用户数量
            Map<Object, LocalDateTime> timeMap = new HashMap<>();
            //用户总数
            timeMap.put("endTime", endTime);
            //查询当前之前的用户数量
            Long userNumber = userMapper.findUserNumberByCreateTime(timeMap);
            userList.add(userNumber);
            //新增用户数
            Map<Object, LocalDateTime> newTimeMap = new HashMap<>();
            newTimeMap.put("startTime", startTime);
            newTimeMap.put("endTime", endTime);
            //查询当天的用户数量
            Long userNumberBefore = userMapper.findUserNumberByCreateTime(newTimeMap);
            newUserList.add(userNumberBefore);
        }
        //获取用户总量的字符串，以","分隔
        String totalUserString = StringUtils.join(userList, ",");

        //3.获取日期范围中每天新增用户数
        log.info("新增用户数量列表: {}", newUserList);
        String newUserString = StringUtils.join(newUserList, ",");

        //封装数据并返回
        return UserReportVO.builder()
                .dateList(dateString)
                .totalUserList(totalUserString)
                .newUserList(newUserString)
                .build();
    }

    /**
     * 订单统计
     * @param start
     * @param end
     * @return
     */
    @Override
    public OrderReportVO getOrdersStatistics(LocalDate start, LocalDate end) {

        //保存搜索初始条件
        LocalDate start0 = start;
        LocalDate end0 = end;

        //1.日期字符串列表
        List<LocalDate> dateList = new ArrayList();
        dateList.add(start);
        while(!start.equals(end)){
            start = start.plusDays(1);
            dateList.add(start);
        }
        String dateString = StringUtils.join(dateList, ",");

        //获取开始时间的最小时间
        LocalDateTime startTime0 = LocalDateTime.of(start0,LocalTime.MIN);
        //获取结束时间的最大时间
        LocalDateTime endTime0 = LocalDateTime.of(end0,LocalTime.MAX);
        //查询时间范围内的全部订单
        List<Orders> orders = orderMapper.findOrdersByOrderTime(startTime0, endTime0);

        //2.有效订单数 -->5已完成
        //定义存储有效订单数的集合
        List<Integer> validOrderList = new ArrayList();
        //总的有效订单数
        Integer ValidOrderCount = 0;
        //获取单天中的有效订单数
        for(LocalDate date: dateList){

            //获取当天时间最小值
            LocalDateTime startTime = LocalDateTime.of(date,LocalTime.MIN);
            //获取当天时间最大值
            LocalDateTime endTime = LocalDateTime.of(date,LocalTime.MAX);

            //计算有效订单数量的索引
            Integer count = 0;
            for(Orders order: orders){
                if(order.getOrderTime().isBefore(endTime) && order.getOrderTime().isAfter(startTime)){
                    //判断有效订单  -->5已完成
                    if(order.getStatus().equals(Orders.COMPLETED)){
                        count++;
                    }
                };
            }
            validOrderList.add(count);
        }

        //获取总的有效订单数
        ValidOrderCount = validOrderList.stream().reduce(Integer::sum).get();

        //3.有效订单数字符串列表
        String vaildOrderString = StringUtils.join(validOrderList, ",");

        //定义存储订单数量的集合
        List<Integer> orderList = new ArrayList();
        //获取单日订单数
        for(LocalDate date: dateList){

            //获取当天时间最小值
            LocalDateTime startTime = LocalDateTime.of(date,LocalTime.MIN);
            //获取当天时间最大值
            LocalDateTime endTime = LocalDateTime.of(date,LocalTime.MAX);

            //计算订单数量的索引
            Integer count = 0;
            for(Orders order: orders){
                if(order.getOrderTime().isBefore(endTime) && order.getOrderTime().isAfter(startTime)){
                    count++;
                };
            }
            orderList.add(count);
        }

        //4.订单总数
        Integer totalOrderCount = 0;
        totalOrderCount = orderList.stream().reduce(Integer::sum).get();

        //5.订单数字符串列表
        String orderString = StringUtils.join(orderList, ",");

        //6.订单总完成率--> 总的有效订单数/订单总数
        Double OrderCompletionRate = (double)ValidOrderCount/(double)totalOrderCount;

        return OrderReportVO.builder()
                .dateList(dateString)
                .orderCompletionRate(OrderCompletionRate)
                .orderCountList(orderString)
                .totalOrderCount(totalOrderCount)
                .validOrderCount(ValidOrderCount)
                .validOrderCountList(vaildOrderString)
                .build();
    }

    /**
     * 查询销量排名top10
     * @param begin
     * @param end
     * @return
     */
    @Override
    public SalesTop10ReportVO getTop10(LocalDate begin, LocalDate end) {

        LocalDateTime start = LocalDateTime.of(begin,LocalTime.MIN);
        LocalDateTime end1 = LocalDateTime.of(end,LocalTime.MAX);

        //查询商品名称和销量
        List<SalesTop10ReportVO> salesTop10 = orderMapper.getNameAndCount(start, end1);

        //存储商品名称
        List<String> nameList0 = new ArrayList();
        //存储销量
        List<String> numberList0 = new ArrayList();

        for(SalesTop10ReportVO name: salesTop10){
            nameList0.add(name.getNameList());
            numberList0.add(name.getNumberList());
        }
        String nameList = StringUtils.join(nameList0, ",");
        String numberList = StringUtils.join(numberList0, ",");

        return SalesTop10ReportVO.builder()
                .nameList(nameList)
                .numberList(numberList)
                .build();
    }

    /**
     * 导出数据报表
     * @param response
     */
    @Override
    public void exportBusinessData(HttpServletResponse response){
        //1.获取前30天的统计数据
        //获取前三十天的那天日期
        LocalDate start = LocalDate.now().plusDays(-30);
        //获取昨天的日期
        LocalDate end = LocalDate.now().plusDays(-1);

        //保存原始起始日期
        LocalDate start0 = start;
        LocalDate end0 = end;

        //获取总统计数据
        BusinessDataVO businessData = workspaceService.getBusinessData(
                LocalDateTime.of(start0,LocalTime.MIN),LocalDateTime.of(end0,LocalTime.MAX));
        //获取单日的统计数据集合
        List<LocalDate> dateList = new ArrayList();
        dateList.add(start);
        while(!start.equals(end)){
            start = start.plusDays(1);
            dateList.add(start);
        }
        List<BusinessDataVO> businessDataList = new ArrayList();
        for(LocalDate date: dateList){
            businessDataList
                    .add(workspaceService.getBusinessData(LocalDateTime.of(date,LocalTime.MIN),
                            LocalDateTime.of(date,LocalTime.MAX)));
        }


        try {
            //2.使用Apache POI将统计数据写入到excel模版中
            //获取模版
            InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");
            XSSFWorkbook excel = new XSSFWorkbook(in);
            //获取excel文档的sheet表单
            XSSFSheet sheet = excel.getSheetAt(0);

            //输入数据
            //第二行第二单元格
            writeExcel(sheet,1,1,"日期：" + start0 + "至" + end0);
            //第四行第三单元格
//            writeExcel(sheet,3, 2, String.valueOf(businessData.getTurnover()));
            sheet.getRow(3).getCell(2).setCellValue(businessData.getTurnover());
            //第四行第五单元格
//            writeExcel(sheet,3, 4, String.valueOf(businessData.getOrderCompletionRate()));
            sheet.getRow(3).getCell(4).setCellValue(businessData.getOrderCompletionRate());
            //第四行第七单元格
            writeExcel(sheet,3, 6, String.valueOf(businessData.getNewUsers()));
            //第五行第三单元格
            writeExcel(sheet,4, 2, String.valueOf(businessData.getValidOrderCount()));
            //第五行第五单元格
//            writeExcel(sheet,4, 4, String.valueOf(businessData.getUnitPrice()));
            sheet.getRow(4).getCell(4).setCellValue(businessData.getUnitPrice());
            //第八行及之后的2-7单元格
            for(int i =0,j = 7; i < businessDataList.size(); i++,j++){
                //第j行第2单元格
                writeExcel(sheet, j, 1, String.valueOf(dateList.get(i)));
                //第j行第3单元格
//                writeExcel(sheet, j, 2, String.valueOf(businessDataList.get(i).getTurnover()));
                sheet.getRow(j).getCell(2).setCellValue(businessDataList.get(i).getTurnover());
                //第j行第4单元格
                writeExcel(sheet, j, 3, String.valueOf(businessDataList.get(i).getValidOrderCount()));
                //第j行第5单元格
//                writeExcel(sheet, j, 4, String.valueOf(businessDataList.get(i).getOrderCompletionRate()));
                sheet.getRow(j).getCell(4).setCellValue(businessDataList.get(i).getOrderCompletionRate());
                //第j行第6单元格
//                writeExcel(sheet, j, 5, String.valueOf(businessDataList.get(i).getUnitPrice()));
                sheet.getRow(j).getCell(5).setCellValue(businessDataList.get(i).getUnitPrice());
                //第j行第7单元格
                writeExcel(sheet, j, 6, String.valueOf(businessDataList.get(i).getNewUsers()));
            }

            //3.通过输出流将excel下载到客户端浏览器
            ServletOutputStream out = response.getOutputStream();
            excel.write(out);
            //关闭资源
            out.close();
            excel.close();
        } catch (IOException e) {
            throw new RuntimeException("导出数据报表失败");
        }
    }

    //填写Excel模版文件数据
    private void writeExcel(XSSFSheet sheet, int row, int cell, String value){
        sheet.getRow(row).getCell(cell).setCellValue(value);
    }
}
